
df=sqlContent.sql("""
    INSERT  INTO TABLE imei.test_result_20190122 select * from (select imei,md5(imei),msisdn, FROM dw.imei_all
    WHERE md5(imei) IN (SELECT  imei FROM imei.test_20190122))
""")
import pandas as pd

df=sqlContext.sql("""
    select * FROM imei.test_result_20190122
""")
writer = pd.ExcelWriter("""/home/extract/imei/20190123/20190123.xlsx""" % {"dir":dir},
                        engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()




df=sqlContext.sql("""
    CREATE TABLE dw.xinyongka_20190126 as
    select
        *
    FROM
        dw.sms_log
    WHERE pt>='201805' and
        smslabel ='百度'
        AND (msg_content RLIKE '.*百度有钱花.*自动还款.*'
        OR msg_content RLIKE '.*自动还款.*'
        OR msg_content RLIKE '.*大额尊享.提醒您.*')
""")


df=sqlContext.sql("""
    CREATE TABLE dw.xinyongka_20190217 as
    select
        *
    FROM
        dw.sms_log
    WHERE
        msg_content LIKE '*理财*'
        OR msg_content LIKE '*车贷*'
        OR msg_content LIKE '*房贷*'
        or smslabel like '*证券*'
""")

df=sqlContext.sql("""
    CREATE TABLE dw.lianjiang_20190220 as
    select
        *
    FROM
        dw.sms_log
    WHERE
        msg_content LIKE '%连江%'
        OR msg_content LIKE '%凤城镇%'
        OR msg_content LIKE '%敖江镇%'
        OR msg_content LIKE '%江南乡%'
        OR msg_content LIKE '%东湖镇%'
        OR msg_content LIKE '%浦口镇%'
        OR msg_content LIKE '%东岱镇%'
        OR msg_content LIKE '%晓澳镇%'
        OR msg_content LIKE '%琯头镇%'
        OR msg_content LIKE '%潘渡乡%'
        OR msg_content LIKE '%小沧乡%'
        OR msg_content LIKE '%丹阳镇%'
        OR msg_content LIKE '%蓼沿乡%'
        OR msg_content LIKE '%长龙镇%'
        OR msg_content LIKE '%透堡镇%'
        OR msg_content LIKE '%马鼻镇%'
        OR msg_content LIKE '%官坂镇%'
        OR msg_content LIKE '%坑园镇%'
        OR msg_content LIKE '%下宫乡%'
        OR msg_content LIKE '%筱埕镇%'
        OR msg_content LIKE '%黄岐镇%'
        OR msg_content LIKE '%安凯乡%'
        OR msg_content LIKE '%苔菉镇%'
""")


df=sqlContext.sql("""
    select msisdn,city FROM
     dw.xinyongka_20190126
    WHERE
        prov_id = 16 or
        area in ('023','024','0411','022','028')

""")


df=sqlContext.sql("""
    select msisdn,city FROM
     dw.xinyongka_20190217
    WHERE
        prov_id = 16 AND provide_id <> 3
""")

writer = pd.ExcelWriter("""/home/extract/imei/20190217/20190217.xlsx""" % {"dir":dir},
                        engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()